import sqlite3,os

def db_init():
    db1 = sqlite3.connect( os.path.join(os.getcwd(), 'storge.db') )
    cu1 = db1.cursor()
    #初始化 XMPP 连接为防止阻塞，可放在线程中进行
    cu1.execute('CREATE TABLE IF NOT EXISTS servers (domain text not null unique, \
                                                     port integer not null, \
                                                     status integer default 0 )')
    #初始化载入共享文件列表需要验证文件的MD5,为防止阻塞，可放在线程中进行
    cu1.execute('create table if not exists shared (keyname text unique not null, pathname text not null)')
    cu1.close()
    db1.commit()
    db1.close()
db_init()

def add_shared(pathname,sharename):
    db1 = sqlite3.connect( os.path.join(os.getcwd(), 'storge.db') )
    cu1 = db1.cursor()
    res = True
    try:
        cu1.execute('insert into shared (keyname, pathname) values (?,?)',(sharename,pathname))
    except:
        res = False
    cu1.close()
    db1.commit()
    db1.close()
    return res
    
def del_shared(sharename):
    db1 = sqlite3.connect( os.path.join(os.getcwd(), 'storge.db') )
    cu1 = db1.cursor()
    cu1.execute('delete from shared where keyname=?',(sharename,))
    cu1.close()
    db1.commit()
    db1.close()
    
def get_shared():
    db1 = sqlite3.connect( os.path.join(os.getcwd(), 'storge.db') )
    cu1 = db1.cursor()
    cu1.execute('select pathname,keyname from shared')
    ret = []
    for item in cu1:
        ret.append( [ item[0],item[1] ] )
    cu1.close()
    db1.commit()
    db1.close()
    return ret

def add_server(domain,port):
    db1 = sqlite3.connect( os.path.join(os.getcwd(), 'storge.db') )
    cu1 = db1.cursor()
    #初始化 XMPP 连接为防止阻塞，可放在线程中进行
    #servers (domain text not null,  port integer not null, status integer default 0 )')
    cu1.execute('insert or replace into servers (domain, port) values (?,?)',(domain,port))
    cu1.close()
    db1.commit()
    db1.close()
    
def del_server(domain):
    db1 = sqlite3.connect( os.path.join(os.getcwd(), 'storge.db') )
    cu1 = db1.cursor()
    #初始化 XMPP 连接为防止阻塞，可放在线程中进行
    #servers (domain text not null,  port integer not null, status integer default 0 )')
    cu1.execute('delete from servers where domain=?',(domain,))
    cu1.close()
    db1.commit()
    db1.close()
    
def get_servers():
    db1 = sqlite3.connect( os.path.join(os.getcwd(), 'storge.db') )
    cu1 = db1.cursor()
    cu1.execute('select domain,port from servers')
    for item in cu1:
        yield [item[0],item[1],'未连接']
    cu1.close()
    db1.commit()
    db1.close()
